﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using COMExcel=Microsoft.Office.Interop.Excel;
using DTOQLDV;

namespace DAOQLDV
{
    public class DAOXuLi
    {
        public static List<DTODuLieuEX> DocDuLieuTuFileExcel(String filePath)
        {
            List<DTODuLieuEX> dsDuLieu = new List<DTODuLieuEX>();
            String connString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0";
            String sql = @"Select * From [Sheet1$]";
            OleDbConnection cnn = new OleDbConnection(connString);
            try
            {
                cnn.Open();
                OleDbCommand cmd = new OleDbCommand(sql, cnn);
                OleDbDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    DTODuLieuEX dtoDLEx = new DTODuLieuEX();
                    dtoDLEx.TenDichVu = dr[0].ToString();
                    dtoDLEx.TenDiaDiem = dr[1].ToString();
                    dtoDLEx.SoNha = dr[2].ToString();
                    dtoDLEx.Duong = dr[3].ToString();
                    dtoDLEx.Phuong = dr[4].ToString();
                    dtoDLEx.QuanHuyen = dr[5].ToString();
                    dtoDLEx.TinhThanh = dr[6].ToString();
                    dtoDLEx.KinhDo = dr[7].ToString();
                    dtoDLEx.ViDo = dr[8].ToString();
                    dtoDLEx.ChuThich = dr[9].ToString();
                    dsDuLieu.Add(dtoDLEx);
                }
                dr.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cnn.Close();
            }
            return dsDuLieu;
        }
        public static bool XuatDuLieuRaFileExcel(String fileName, DataTable dt)
        {
            bool kq = false;
            return kq;
        }
        public static bool KiemTraKetNoi()
        {
            DataAccess dp = new DataAccess();
            bool kq = false;
            try
            {
                dp.Open();                
               kq= true;
            }
            catch (Exception ex)
            {
            }
            finally
            {
                dp.Close();
            }
            return kq;
        }
        public static List<String> dsTuKhoa(String ten)
        {
            List<String> dsTuKhoa = new List<string>();
            if (ten.Equals(""))
            {
                String[] tkarr = new String[4];
                tkarr[0] = ten.ToLower();
                tkarr[1] = ten.ToUpper();
                tkarr[2] = ten.ToLower().Replace(" ", "");
                tkarr[3] = ten.ToUpper().Replace(" ", "");
                for (int i = 0; i < 4; i++) 
                {
                    dsTuKhoa.Add(tkarr[i]);
                }
            }
            return dsTuKhoa;
        }
    }
}
